﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class clsDA_HomeIndex
    Private _SqlCnn As SqlConnection
    Private _connectionString As String
    Public Sub New()
        ' Read the attributes for this provider
        If System.Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString <> "" Then
            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString
        End If
        _SqlCnn = New SqlConnection(_connectionString)
        Try
            _SqlCnn.Open()
        Catch ex As Exception
            _SqlCnn = Nothing
        End Try
    End Sub
    Public Sub Destructor()
        If Not _SqlCnn Is Nothing Then
            If _SqlCnn.State <> Data.ConnectionState.Closed Then _SqlCnn.Close()
            _SqlCnn.Dispose()
            _SqlCnn = Nothing
        End If
    End Sub
    'Select Menu, SubMenu, Content of menu to show home
    Public Function Select_Content_Home(ByVal iMenuId As Integer) As DataSet
        Dim dt_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectMenuContentHome"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@menuID", SqlDbType.Int).Value = iMenuId
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function
    Public Function Select_Content_HomeOriginal(ByVal iMenuId As Integer) As DataSet
        Dim dt_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectMenuContentHomeSSMOriginal"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@menuID", SqlDbType.Int).Value = iMenuId
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function
    'Get Expert Ask and Expert Live Chat
    Public Function Select_Expert_Home() As DataSet
        Dim dt_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectExpertHome"
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function

    'Get contest show home
    Public Function Select_Contest_Home() As DataTable
        Dim dt_all As New DataTable
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectContestHome"
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function

    'Get Menu Footer
    Public Function Select_Menu_Footer() As DataSet
        Dim ds_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspGetMenuFooter"
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(ds_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return ds_all
    End Function

    'Get top 1 content of menu root
    Public Function Select_Top_One_Menu_Root(Optional ByVal iContentId As Integer = 0) As DataTable
        Dim dt_all As New DataTable
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectTop1ContentOfMenuRoot"
                .Parameters.Add("@ContentId", SqlDbType.Int).Value = iContentId
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function

    'Get top 1 bai viet moi nhat of menu root
    Public Function Select_Top_One_Newest_Menu_Root(ByVal iMenuId As Integer) As DataTable
        Dim dt_all As New DataTable
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "Select * From dbo.ufnGetTop1ContentOfMenuId('" & iMenuId & "')"
                .CommandType = CommandType.Text
                sqlAdp.Fill(dt_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return dt_all
    End Function
    'Get content page index what cook today
    Public Function Select_Content_Home_WhatCookToday() As DataSet
        Dim ds_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectFormulaType"
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(ds_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return ds_all
    End Function

    'Get content top 1 od menu consultant
    Public Function Select_Content_Top1_Consultant(ByVal iMenuConsultant As Integer) As DataSet
        Dim ds_all As New DataSet
        Dim sqlAdp As New SqlClient.SqlDataAdapter
        Dim sqlTrans As SqlClient.SqlTransaction = _SqlCnn.BeginTransaction()
        sqlAdp.SelectCommand = New SqlClient.SqlCommand
        With sqlAdp.SelectCommand
            Try
                .Connection = _SqlCnn
                .Transaction = sqlTrans
                .CommandText = "uspSelectTop1ContentOfConsultant"
                .Parameters.Add("@MenuId", SqlDbType.Int).Value = iMenuConsultant
                .CommandType = CommandType.StoredProcedure
                sqlAdp.Fill(ds_all)
                sqlTrans.Commit()
            Catch Exp As SqlClient.SqlException
                sqlTrans.Rollback()
            Finally
                .Parameters.Clear()
            End Try
        End With
        If Not sqlAdp Is Nothing Then
            If Not sqlAdp.SelectCommand Is Nothing Then
                sqlAdp.SelectCommand.Dispose()
            End If
            sqlAdp.Dispose()
        End If
        Return ds_all
    End Function
End Class
